--source include/elide_costs.inc

#
# Test file for WL#1724 (Min/Max Optimization for Queries with Group By Clause).
# The queries in this file test query execution via GroupIndexSkipScan.
#

#
# TODO:
# Add queries with:
# - C != const
# - C IS NOT NULL
# - HAVING clause

# Note: Wherever temp table is cheaper than index skip scan, the latter is
# forced by sql_big_result so that we don't defeat the purpose of the test.

--disable_warnings
drop table if exists t1;
--enable_warnings

create table t1 (
  a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(248) default ' '
) charset latin1;

insert into t1 (a1, a2, b, c, d) values
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');

create index idx_t1_0 on t1 (a1);
create index idx_t1_1 on t1 (a1,a2,b,c);
create index idx_t1_2 on t1 (a1,a2,b);
analyze table t1;

# t2 is the same as t1, but with some NULLs in the MIN/MAX column, and
# one more nullable attribute

--disable_warnings
drop table if exists t2;
--enable_warnings

create table t2 (
  a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(248) default ' '
) charset latin1;
insert into t2 select * from t1;
# add few rows with NULL's in the MIN/MAX column
insert into t2 (a1, a2, b, c, d) values
('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),
('a','a','a',NULL,'xyz'),
('a','a','b',NULL,'xyz'),
('a','b','a',NULL,'xyz'),
('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'),
('d','b','b',NULL,'xyz'),
('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),
('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),
('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'),
('a','a','a',NULL,'xyz'),
('a','a','b',NULL,'xyz'),
('a','b','a',NULL,'xyz'),
('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'),
('d','b','b',NULL,'xyz'),
('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),
('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz');

create index idx_t2_0 on t2 (a1);
create index idx_t2_1 on t2 (a1,a2,b,c);
create index idx_t2_2 on t2 (a1,a2,b);
analyze table t2;

# Table t3 is the same as t1, but with smaller column lenghts.
# This allows to test different branches of the cost computation procedure
# when the number of keys per block are less than the number of keys in the
# sub-groups formed by predicates over non-group attributes.

--disable_warnings
drop table if exists t3;
--enable_warnings

create table t3 (
  a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' '
) charset latin1;

insert into t3 (a1, a2, b, c, d) values
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
insert into t3 (a1, a2, b, c, d) values
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
insert into t3 (a1, a2, b, c, d) values
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');
insert into t3 (a1, a2, b, c, d) values
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4');

create index idx_t3_0 on t3 (a1);
create index idx_t3_1 on t3 (a1,a2,b,c);
create index idx_t3_2 on t3 (a1,a2,b);
analyze table t3;


#
# Queries without a WHERE clause. These queries do not use ranges.
#

# plans
--replace_regex $elide_costs
explain format=tree select a1, min(a2) from t1 group by a1;
--replace_regex $elide_costs
explain format=tree select a1, min(a2) from t1 group by a1;  # Also demonstrates lack of AggregateIterator.
--replace_regex $elide_costs
explain format=tree select a1, max(a2) from t1 group by a1;
--replace_regex $elide_costs
explain format=tree select a1, min(a2), max(a2) from t1 group by a1;
--replace_regex $elide_costs
explain format=tree select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
--replace_column 8 # 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
# Select fields in different order
--replace_regex $elide_costs
explain format=tree select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
--replace_regex $elide_costs
explain format=tree select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
--replace_regex $elide_costs
explain format=tree select min(a2) from t1 group by a1;
--replace_regex $elide_costs
explain format=tree select a2, min(c), max(c) from t1 group by a1,a2,b;

# queries
select a1, min(a2) from t1 group by a1;
select a1, max(a2) from t1 group by a1;
select a1, min(a2), max(a2) from t1 group by a1;
select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b;
select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b;
select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b;
# Select fields in different order
select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1;
select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b;
select min(a2) from t1 group by a1;
select a2, min(c), max(c) from t1 group by a1,a2,b;

#
# Queries with a where clause
#

# A) Preds only over the group 'A' attributes
# plans
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1, max(c)            from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,min(c),max(c)      from t1 where a1 >= 'b' group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,  max(c)           from t1 where a1 in ('a','b','d') group by a1,a2,b;

--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t2 where a1 < 'd' group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1, max(c)            from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,min(c),max(c)      from t2 where a1 >= 'b' group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,  max(c)           from t2 where a1 in ('a','b','d') group by a1,a2,b;

# queries
select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
select a1,a2,b,       max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
select a1, max(c)            from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
select a1,a2,b,       max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
select a1,a2,b,       max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
select a1,min(c),max(c)      from t1 where a1 >= 'b' group by a1,a2,b;
select a1,  max(c)           from t1 where a1 in ('a','b','d') group by a1,a2,b;

select a1,a2,b,       max(c) from t2 where a1 < 'd' group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
select a1,a2,b,       max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
select a1, max(c)            from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
select a1,a2,b,       max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b;
select a1,a2,b,       max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b;
select a1,min(c),max(c)      from t2 where a1 >= 'b' group by a1,a2,b;
select a1,  max(c)           from t2 where a1 in ('a','b','d') group by a1,a2,b;

# B) Equalities only over the non-group 'B' attributes
# plans

--replace_regex $elide_costs
explain format=tree select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
--replace_regex $elide_costs
explain format=tree select a1,max(c),min(c)      from t1 where (a2 = 'a') and (b = 'b') group by a1;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t1 where (b = 'b') group by a1,a2;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
--replace_regex $elide_costs
explain format=tree select a1,a2, max(c)         from t1 where (b = 'b') group by a1,a2;

--replace_regex $elide_costs
explain format=tree select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
--replace_regex $elide_costs
explain format=tree select a1,max(c),min(c)      from t2 where (a2 = 'a') and (b = 'b') group by a1;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t2 where (b = 'b') group by a1,a2;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
--replace_regex $elide_costs
explain format=tree select a1,a2, max(c)         from t2 where (b = 'b') group by a1,a2;

# these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost()
--replace_regex $elide_costs
explain format=tree select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
--replace_regex $elide_costs
explain format=tree select a1,max(c),min(c)      from t3 where (a2 = 'a') and (b = 'b') group by a1;

# queries
select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
select a1,max(c),min(c)      from t1 where (a2 = 'a') and (b = 'b') group by a1;
select a1,a2,b,       max(c) from t1 where (b = 'b') group by a1,a2;
select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
select a1,a2, max(c)         from t1 where (b = 'b') group by a1,a2;

select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
select a1,max(c),min(c)      from t2 where (a2 = 'a') and (b = 'b') group by a1;
select a1,a2,b,       max(c) from t2 where (b = 'b') group by a1,a2;
select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
select a1,a2, max(c)         from t2 where (b = 'b') group by a1,a2;

# these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost()
select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1;
select a1,max(c),min(c)      from t3 where (a2 = 'a') and (b = 'b') group by a1;


# IS NULL (makes sense for t2 only)
# plans

# SQL standard does not impose recognition of 'b IS NULL' as
# a functional dependency of {b} on {}.
# These queries are home-grown.

--source include/turn_off_only_full_group_by.inc

--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
# queries
select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1;
select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1;
select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2;
select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2;
select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;
select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2;

--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc

# C) Range predicates for the MIN/MAX attribute
# plans
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t1 where (c > 'b1') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t1 where (c > 'f123') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t1 where (c < 'a0') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t1 where (c < 'k321') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where (c between 'b111' and 'g112') or (c between 'd000' and 'i110') group by a1,a2,b;

--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t2 where (c > 'b1') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t2 where (c > 'f123') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t2 where (c < 'a0') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t2 where (c < 'k321') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,       max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b;

# queries
select a1,a2,b,       max(c) from t1 where (c > 'b1') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b;
select a1,a2,b,       max(c) from t1 where (c > 'f123') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b;
select a1,a2,b,       max(c) from t1 where (c < 'a0') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b;
select a1,a2,b,       max(c) from t1 where (c < 'k321') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b;
select a1,a2,b,       max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
select a1,a2,b,       max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t1 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t1 where (c between 'b111' and 'g112') or (c between 'd000' and 'i110') group by a1,a2,b;

select a1,a2,b,       max(c) from t2 where (c > 'b1') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b;
select a1,a2,b,       max(c) from t2 where (c > 'f123') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b;
select a1,a2,b,       max(c) from t2 where (c < 'a0') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b;
select a1,a2,b,       max(c) from t2 where (c < 'k321') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b;
select a1,a2,b,       max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b;
select a1,a2,b,       max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t2 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b;

# analyze the sub-select
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1
where exists ( select * from t2 where t2.c = t1.c )
group by a1,a2,b;

# the sub-select is unrelated to MIN/MAX
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1
where exists ( select * from t2 where t2.c > 'b1' )
group by a1,a2,b;


# A,B,C) Predicates referencing mixed classes of attributes
# plans
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c) from t1 where (ord(a1) > 97) and (ord(a2) + ord(a1) > 194) and (b = 'c') group by a1,a2,b;

--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;

# queries
select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
select a1,a2,b,min(c) from t1 where (ord(a1) > 97) and (ord(a2) + ord(a1) > 194) and (b = 'c') group by a1,a2,b;

select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b;
select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b;
select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9'))  and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;


#
# GROUP BY queries without MIN/MAX
#

# plans
--replace_regex $elide_costs
explain format=tree select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;

--replace_regex $elide_costs
explain format=tree select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;

--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;

# queries
select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;

select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;

#
# DISTINCT queries
#

# plans
--replace_regex $elide_costs
explain format=tree select sql_big_result distinct a1,a2,b from t1;
--replace_regex $elide_costs
explain format=tree select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
--replace_regex $elide_costs
explain format=tree select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
--replace_regex $elide_costs
explain format=tree select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
--replace_regex $elide_costs
explain format=tree select distinct b from t1 where (a2 >= 'b') and (b = 'a');

--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select sql_big_result distinct a1,a2,b from t2;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select sql_big_result distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
--replace_regex $elide_costs
explain format=tree select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
--replace_regex $elide_costs
explain format=tree select distinct b from t2 where (a2 >= 'b') and (b = 'a');

# queries
select sql_big_result distinct a1,a2,b from t1;
select sql_big_result distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
select distinct b from t1 where (a2 >= 'b') and (b = 'a');

select sql_big_result distinct a1,a2,b from t2;
select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
select distinct b from t2 where (a2 >= 'b') and (b = 'a');

# BUG #6303
select distinct t_00.a1
from t1 t_00
where exists ( select * from t2 where a1 = t_00.a1 );

# BUG #8532 - SELECT DISTINCT a, a causes server to crash
select distinct a1,a1 from t1;
--sorted_result
select distinct a2,a1,a2,a1 from t1;
--sorted_result
select distinct t1.a1,t2.a1 from t1,t2;


#
# DISTINCT queries with GROUP-BY
#

# plans

#--source include/turn_off_only_full_group_by.inc

--replace_regex $elide_costs
explain format=tree select sql_big_result distinct a1,a2,b from t1;
--replace_regex $elide_costs
explain format=tree select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;

--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select sql_big_result distinct a1,a2,b from t2;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
--replace_column 10 #
--replace_regex $elide_costs
explain format=tree select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;

# queries
select distinct sql_big_result a1,a2,b from t1;
select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;

select sql_big_result distinct a1,a2,b from t2;
select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;

#--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc

#
# COUNT (DISTINCT cols) queries
#

--replace_regex $elide_costs
explain format=tree select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
--replace_regex $elide_costs
explain format=tree select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
--replace_regex $elide_costs
explain format=tree select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
--replace_regex $elide_costs
explain format=tree select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
--replace_regex $elide_costs
explain format=tree select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');

select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');

#
# Queries with expressions in the select clause
#

--replace_regex $elide_costs
explain format=tree select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;

select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;


#
# Negative examples: queries that should NOT be treated as optimizable by
# GroupIndexSkipScan
#

# select a non-indexed attribute

--source include/turn_off_only_full_group_by.inc

--replace_regex $elide_costs
explain format=tree select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b;

--replace_regex $elide_costs
explain format=tree select a1,a2,b,d from t1 group by a1,a2,b;

# predicate that references an attribute that is after the MIN/MAX argument
# in the index
--replace_regex $elide_costs
explain format=tree select a1,a2,min(b),max(b) from t1
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2;

# predicate that references a non-indexed attribute
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t1
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b;

--replace_regex $elide_costs
explain format=tree select a1,a2,b,c from t1
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c;

# non-equality predicate for a non-group select attribute
--replace_regex $elide_costs
explain format=tree select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1;
--replace_regex $elide_costs
explain format=tree select a1,a2,b from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2,b;

# non-group field with an equality predicate that references a keypart after the
# MIN/MAX argument
--replace_regex $elide_costs
explain format=tree select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;

# disjunction for a non-group select attribute
--replace_regex $elide_costs
explain format=tree select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1;

--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc

# non-range predicate for the MIN/MAX attribute
--replace_regex $elide_costs
explain format=tree select a1,a2,b,min(c),max(c) from t2
where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b;

# not all attributes are indexed by one index
--replace_regex $elide_costs
explain format=tree select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c;

# other aggregate functions than MIN/MAX
--replace_regex $elide_costs
explain format=tree select a1,a2,count(a2) from t1 group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
--replace_regex $elide_costs
explain format=tree select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;


#
# Bug #16710: select distinct doesn't return all it should
#

--replace_regex $elide_costs
explain format=tree select sql_big_result distinct(a1) from t1 where ord(a2) = 98;
select sql_big_result distinct(a1) from t1 where ord(a2) = 98;

#
# BUG#11044: DISTINCT or GROUP BY queries with equality predicates instead of MIN/MAX.
#

--replace_regex $elide_costs
explain format=tree select a1 from t1 where a2 = 'b' group by a1;
select a1 from t1 where a2 = 'b' group by a1;

--replace_regex $elide_costs
explain format=tree select distinct a1 from t1 where a2 = 'b';
select distinct a1 from t1 where a2 = 'b';

#
# Bug #12672: primary key implcitly included in every innodb index
#
# Test case moved to group_min_max_innodb


#
# Bug #6142: a problem with the empty innodb table
#
# Test case moved to group_min_max_innodb


#
# Bug #9798: group by with rollup
#
# Test case moved to group_min_max_innodb


#
# Bug #13293 Wrongly used index results in endless loop.
#
# Test case moved to group_min_max_innodb


drop table t1,t2,t3;

#
# Bug #14920 Ordering aggregated result sets with composite primary keys
# corrupts resultset
#
create table t1 (c1 int not null,c2 int not null, primary key(c1,c2));
insert into t1 (c1,c2) values
(10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9);
select distinct c1, c2 from t1 order by c2;
select c1,min(c2) as c2 from t1 group by c1 order by c2;
select c1,c2 from t1 group by c1,c2 order by c2;
drop table t1;

#
# Bug #16203: Analysis for possible min/max optimization erroneously
#             returns impossible range
#

CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b)) charset utf8mb4;
INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4);
OPTIMIZE TABLE t1;

SELECT a FROM t1 WHERE a='AA' GROUP BY a;
SELECT a FROM t1 WHERE a='BB' GROUP BY a;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT a FROM t1 WHERE a='AA' GROUP BY a;
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT a FROM t1 WHERE a='BB' GROUP BY a;

SELECT DISTINCT a FROM t1 WHERE a='BB';
SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%';
SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a;

DROP TABLE t1;


#
# Bug #15102: select distinct returns empty result, select count
#             distinct > 0 (correct)
#

CREATE TABLE t1 (
   a int(11) NOT NULL DEFAULT '0',
   b varchar(16) COLLATE latin1_general_ci NOT NULL DEFAULT '',
   PRIMARY KEY  (a,b)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

delimiter |;

CREATE PROCEDURE a(x INT)
BEGIN
  DECLARE rnd INT;
  DECLARE cnt INT;

  WHILE x > 0 DO
    SET rnd= x % 100;
    SET cnt = (SELECT COUNT(*) FROM t1 WHERE a = rnd);
    INSERT INTO t1(a,b) VALUES (rnd, CAST(cnt AS CHAR));
    SET x= x - 1;
  END WHILE;
END|

DELIMITER ;|

CALL a(1000);

SELECT a FROM t1 WHERE a=0;
SELECT DISTINCT a FROM t1 WHERE a=0;
SELECT COUNT(DISTINCT a) FROM t1 WHERE a=0;

DROP TABLE t1;
DROP PROCEDURE a;

#
# Bug #18068: SELECT DISTINCT
#

CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a)) charset utf8mb4;

INSERT INTO t1 (a) VALUES
  (''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'),
  ('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'),
  ('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN');

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT DISTINCT a,a FROM t1 ORDER BY a;
SELECT DISTINCT a,a FROM t1 ORDER BY a;

DROP TABLE t1;

#
# Bug #21007: NATURAL JOIN (any JOIN (2 x NATURAL JOIN)) crashes the server
#

CREATE TABLE t1 (id1 INT, id2 INT);
CREATE TABLE t2 (id2 INT, id3 INT, id5 INT);
CREATE TABLE t3 (id3 INT, id4 INT);
CREATE TABLE t4 (id4 INT);
CREATE TABLE t5 (id5 INT, id6 INT);
CREATE TABLE t6 (id6 INT);

INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1,1);
INSERT INTO t3 VALUES(1,1);
INSERT INTO t4 VALUES(1);
INSERT INTO t5 VALUES(1,1);
INSERT INTO t6 VALUES(1);

# original bug query
SELECT * FROM
t1
  NATURAL JOIN
(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
    ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));

# inner join swapped
SELECT * FROM
t1
  NATURAL JOIN
(((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6) on t3.id4 = t5.id5) JOIN t2
    ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));

# one join less, no ON cond
SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2);

# wrong error message: 'id2' - ambiguous column
SELECT * FROM
(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
    ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5))
  NATURAL JOIN
t1;
SELECT * FROM
(t2 JOIN ((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6)))
  NATURAL JOIN
t1;

DROP TABLE t1,t2,t3,t4,t5,t6;

#
# Bug#22342: No results returned for query using max and group by
#
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
INSERT INTO t1 VALUES (2,1),(2,2),(2,0),(2,3);
INSERT INTO t1 VALUES (3,1),(3,2),(3,0),(3,3);
ANALYZE TABLE t1;

--replace_regex $elide_costs
explain format=tree SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
INSERT INTO t2 SELECT a,b,b FROM t1;
ANALYZE TABLE t2;
--replace_regex $elide_costs
explain format=tree SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;

DROP TABLE t1,t2;

# HANDLER_READ_KEY count differs when using ps protocol because of prepare
# phase of ps protocol results in call to innodb storage engine apis to check
# for table existence.
--disable_ps_protocol
#
# Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar statements
#
CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
       (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6),
       (5,1), (5,2), (5,3), (5,4), (5,5);
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT max(b), a FROM t1 GROUP BY a;
FLUSH STATUS;
SELECT max(b), a FROM t1 GROUP BY a;
SHOW STATUS LIKE 'handler_read__e%';
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT max(b), a FROM t1 GROUP BY a;
FLUSH STATUS;
CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a;
SHOW STATUS LIKE 'handler_read__e%';
FLUSH STATUS;
SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b;
SHOW STATUS LIKE 'handler_read__e%';
FLUSH STATUS;
(SELECT max(b), a FROM t1 GROUP BY a) UNION
 (SELECT max(b), a FROM t1 GROUP BY a);
SHOW STATUS LIKE 'handler_read__e%';
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE (SELECT max(b), a FROM t1 GROUP BY a) UNION
 (SELECT max(b), a FROM t1 GROUP BY a);

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
  FROM t1 AS t1_outer;
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT 1 FROM t1 AS t1_outer WHERE EXISTS
  (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT 1 FROM t1 AS t1_outer WHERE
  (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT 1 FROM t1 AS t1_outer WHERE
  a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING
  a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2
   ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2)
   AND t1_outer1.b = t1_outer2.b;
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
  FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;

CREATE TABLE t3 LIKE t1;
--echo # Warm-up data-dictionary cache.
--disable_result_log
--disable_query_log
SELECT * FROM t3;
--enable_query_log
--enable_result_log
FLUSH STATUS;
INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a;
SHOW STATUS LIKE 'handler_read__e%';
DELETE FROM t3;
FLUSH STATUS;
INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2)
  FROM t1 LIMIT 1;
SHOW STATUS LIKE 'handler_read__e%';
FLUSH STATUS;
DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000;
SHOW STATUS LIKE 'handler_read__e%';
FLUSH STATUS;
--error ER_SUBQUERY_NO_1_ROW
DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x
                      FROM t1) > 10000;
SHOW STATUS LIKE 'handler_read__e%';

DROP TABLE t1,t2,t3;
--enable_ps_protocol

#
# Bug#25602: queries with DISTINCT and SQL_BIG_RESULT hint
#            for which loose scan optimization is applied
#

CREATE TABLE t1 (a int, INDEX idx(a));
INSERT INTO t1 VALUES
  (4), (2), (1), (2), (4), (2), (1), (4),
  (4), (2), (1), (2), (2), (4), (1), (4),
  (4), (2), (1), (2), (2), (4), (1), (4);

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT DISTINCT(a) FROM t1;
SELECT DISTINCT(a) FROM t1;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;

DROP TABLE t1;

#
# Bug #32268: Indexed queries give bogus MIN and MAX results
#

CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3);
INSERT INTO t1 SELECT a + 1, b FROM t1;
INSERT INTO t1 SELECT a + 2, b FROM t1;
INSERT INTO t1 SELECT a + 4, b FROM t1;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;

CREATE INDEX break_it ON t1 (a, b);

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE
SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;

DROP TABLE t1;

#
# Bug#38195: Incorrect handling of aggregate functions when loose index scan is
#            used causes server crash.
#
create table t1 (a int, b int, primary key (a,b), key `index` (a,b)) engine=MyISAM;
insert into  t1 (a,b) values
(0,0),(0,1),(0,2),(0,3),(0,4),(0,5),(0,6),
  (0,7),(0,8),(0,9),(0,10),(0,11),(0,12),(0,13),
(1,0),(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),
  (1,7),(1,8),(1,9),(1,10),(1,11),(1,12),(1,13),
(2,0),(2,1),(2,2),(2,3),(2,4),(2,5),(2,6),
  (2,7),(2,8),(2,9),(2,10),(2,11),(2,12),(2,13),
(3,0),(3,1),(3,2),(3,3),(3,4),(3,5),(3,6),
  (3,7),(3,8),(3,9),(3,10),(3,11),(3,12),(3,13);
insert into t1 (a,b) select a, max(b)+1 from t1 where a = 0 group by a;
--sorted_result
select * from t1;
--replace_regex $elide_costs
explain format=tree select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a;
drop table t1;


#
# Bug #41610: key_infix_len can be overwritten causing some group by queries
# to return no rows
#

CREATE TABLE t1 (a int, b int, c int, d int,
  KEY foo (c,d,a,b), KEY bar (c,a,b,d));

INSERT INTO t1 VALUES (1, 1, 1, 1), (1, 1, 1, 2), (1, 1, 1, 3), (1, 1, 1, 4);
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT a,b,c+1,d FROM t1;

#Should be non-empty
# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT DISTINCT c FROM t1 WHERE d=4;
SELECT DISTINCT c FROM t1 WHERE d=4;

DROP TABLE t1;

--echo #
--echo # Bug #45386: Wrong query result with MIN function in field list,
--echo #  WHERE and GROUP BY clause
--echo #

CREATE TABLE t (a INT, b INT, INDEX (a,b));
INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;

--echo # test MIN
--echo #should use range with index for group by
# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE
SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
--echo #should return 1 row
SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;

--echo # test MAX
--echo #should use range with index for group by
# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE
SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
--echo #should return 1 row
SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;

--echo # test 3 ranges and use the middle one
INSERT INTO t SELECT a, 2 FROM t;

--echo #should use range with index for group by
# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE
SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
--echo #should return 1 row
SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;

DROP TABLE t;

--echo #
--echo # Bug #48472: Loose index scan inappropriately chosen for some WHERE
--echo #             conditions
--echo #

CREATE TABLE t (a INT, b INT, INDEX (a,b));
INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
INSERT INTO t SELECT * FROM t;

SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a;

DROP TABLE t;

--echo End of 5.0 tests

--echo #
--echo # Bug #46607: Assertion failed: (cond_type == Item::FUNC_ITEM) results in
--echo #              server crash
--echo #

CREATE TABLE t (a INT, b INT, INDEX (a,b));
INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
INSERT INTO t SELECT * FROM t;

SELECT a, MAX(b) FROM t WHERE b GROUP BY a;

DROP TABLE t;

#
# BUG#49902 - SELECT returns incorrect results
#
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, KEY (b));
INSERT INTO t1 VALUES(1,1),(2,1);
ANALYZE TABLE t1;
SELECT 1 AS c, b FROM t1 WHERE b IN (1,2) GROUP BY c, b;
SELECT a FROM t1 WHERE b=1;
DROP TABLE t1;

--echo #
--echo # Bug#53859: Valgrind: opt_sum_query(TABLE_LIST*, List<Item>&, Item*) at
--echo # opt_sum.cc:305
--echo #
CREATE TABLE t1 ( a INT, KEY (a) );
INSERT INTO t1 VALUES (1), (2), (3);

SELECT MIN( a ) AS min_a
FROM t1
WHERE a > 1 AND a IS NULL
ORDER BY min_a;

DROP TABLE t1;


--echo End of 5.1 tests


--echo #
--echo # WL#3220 (Loose index scan for COUNT DISTINCT)
--echo #

CREATE TABLE t1 (a INT, b INT, c INT, KEY (a,b));
INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1), (1,4,1);
INSERT INTO t1 SELECT a, b + 4, 1 FROM t1;
INSERT INTO t1 SELECT a + 1, b, 1 FROM t1;
INSERT INTO t1 SELECT a + 2, b + 8, 1 FROM t1;

CREATE TABLE t2 (a INT, b INT, c INT, d INT, e INT, f INT, KEY (a,b,c));
INSERT INTO t2 VALUES (1,1,1,1,1,1), (1,2,1,1,1,1), (1,3,1,1,1,1),
       (1,4,1,1,1,1);
INSERT INTO t2 SELECT a, b + 4, c,d,e,f FROM t2;
INSERT INTO t2 SELECT a + 1, b, c,d,e,f FROM t2;
INSERT INTO t2 SELECT a + 2, b + 8, c,d,e,f FROM t2;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT a) FROM t1;
SELECT COUNT(DISTINCT a) FROM t1;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT a,b) FROM t1;
SELECT COUNT(DISTINCT a,b) FROM t1;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT b,a) FROM t1;
SELECT COUNT(DISTINCT b,a) FROM t1;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT b) FROM t1;
SELECT COUNT(DISTINCT b) FROM t1;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a;
SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;
SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b;
SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
SELECT DISTINCT COUNT(DISTINCT a) FROM t1;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT a, b + 0) FROM t1;
SELECT COUNT(DISTINCT a, b + 0) FROM t1;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 20;
SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 20;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10;
SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10;
SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1;
SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT a), 12 FROM t1;
SELECT COUNT(DISTINCT a), 12 FROM t1;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT a, b, c) FROM t2;
SELECT COUNT(DISTINCT a, b, c) FROM t2;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2;
SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2;
SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2;
SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2;
SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2;
SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c;
SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT c, a, b) FROM t2
  WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c;
SELECT COUNT(DISTINCT c, a, b) FROM t2
  WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5
  GROUP BY b;
SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5
  GROUP BY b;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;
SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42;
SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2
  WHERE b = 13 AND c = 42 GROUP BY a;
SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2
  WHERE b = 13 AND c = 42 GROUP BY a;

--echo # This query could have been resolved using loose index scan since
--echo # the second part of count(..) is defined by a constant predicate
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42;
SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a;
SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a;

# On 32 bit platform the rows estimate is 10 vs 11 on 64 bit platforms.
--replace_result 11 10
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c;
SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a;
SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a;

DROP TABLE t1,t2;

--echo # end of WL#3220 tests

--echo #
--echo # Bug#50539: Wrong result when loose index scan is used for an aggregate
--echo #            function with distinct
--echo #
CREATE TABLE t1 (
  f1 int(11) NOT NULL DEFAULT '0',
  f2 char(1) NOT NULL DEFAULT '',
  PRIMARY KEY (f1,f2)
)  charset utf8mb4;
insert into t1 values(1,'A'),(1 , 'B'), (1, 'C'), (2, 'A'),
(3, 'A'), (3, 'B'), (3, 'C'), (3, 'D');

SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1;
--replace_regex $elide_costs
explain format=tree SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1;

drop table t1;
--echo # End of test#50539.

--echo #
--echo # Bug#18497308 WRONG COST ESTIMATE FOR LOOSE INDEX SCAN WHEN
--echo #              INDEX STATISTICS IS MISSING
--echo #

CREATE TABLE t1 (
  a INTEGER,
  b INTEGER,
  c INTEGER,
  d INTEGER,
  KEY foo (a,b,c,d)
) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1, 1, 1, 1), (1, 2, 1, 2), (1, 3, 1, 3), (1, 4, 1, 4);
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;

let query=
SELECT DISTINCT a FROM t1 WHERE b=4;

--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

DROP TABLE t1;

--echo #
--echo # Bug#17217128 -  BAD INTERACTION BETWEEN MIN/MAX AND
--echo #                 "HAVING SUM(DISTINCT)": WRONG RESULTS.
--echo #

CREATE TABLE t (a INT, b INT, KEY(a,b));
INSERT INTO t VALUES (1,1), (2,2), (3,3), (4,4), (1,0), (3,2), (4,5);
let $DEFAULT_TRACE_MEM_SIZE=1048576; # 1MB
eval set optimizer_trace_max_mem_size=$DEFAULT_TRACE_MEM_SIZE;
set @@session.optimizer_trace='enabled=on';
set end_markers_in_json=on;

ANALYZE TABLE t;

SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a;
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a;
SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK
  FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a;
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a;
SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK
  FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a);
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a);
SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK
  FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t;
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t;
SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK
  FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a;
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a;
SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK
  FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

SET optimizer_trace_max_mem_size=DEFAULT;
SET optimizer_trace=DEFAULT;
SET end_markers_in_json=DEFAULT;

DROP TABLE t;

--echo #
--echo # Bug #18066518: THE COST VALUE IS A NEGATIVE NUMBER FOR MERGE ENGINE
--echo #                TABLE
--echo #
CREATE TABLE t(a INT PRIMARY KEY)
  ENGINE = MERGE;
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT DISTINCT(a) FROM t;
SELECT DISTINCT(a) FROM t;
DROP TABLE t;
--echo # End of test#18066518.

--echo #
--echo # Bug #18486293: ASSERTION FAILED: KEYS >= 0.0 IN
--echo #                COST_MODEL_TABLE::KEY_COMPARE_COST
--echo #
CREATE TABLE t (b INT, KEY b_key (b)) ENGINE=INNODB
  PARTITION BY RANGE COLUMNS(b) (PARTITION p_part VALUES LESS THAN (0));
SELECT 1 FROM t WHERE b IN ('') GROUP BY  b ;
DROP TABLE t;
--echo # End of test#18486293.

--echo #
--echo # Bug#18109609: LOOSE INDEX SCAN IS NOT USED WHEN IT SHOULD
--echo #

CREATE TABLE t1 (
id INT AUTO_INCREMENT PRIMARY KEY,
c1 INT,
c2 INT,
KEY(c1,c2));

INSERT INTO t1(c1,c2) VALUES
(1, 1), (1,2), (2,1), (2,2), (3,1), (3,2), (3,3), (4,1), (4,2), (4,3),
(4,4), (4,5), (4,6), (4,7), (4,8), (4,9), (4,10), (4,11), (4,12), (4,13),
(4,14), (4,15), (4,16), (4,17), (4,18), (4,19), (4,20),(5,5);

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1;
FLUSH STATUS;
SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1;
SHOW SESSION STATUS LIKE 'Handler_read%';

DROP TABLE t1;

--echo # End of test for Bug#18109609

--echo #
--echo # Bug#22661012 - "USING INDEX FOR GROUP-BY" QUERIES CAN RETURN
--echo #                NO DATA WITH LE/LEQ AND ROUNDING
--echo #

CREATE TABLE t1 (a INT, b INT, KEY(a,b));
INSERT INTO t1 VALUES (1,1000), (1,1001), (1,2000),
                      (1,2001), (1,3000), (1,3002);

ANALYZE TABLE t1;

let query= SELECT a, MAX(b) FROM t1 WHERE a = 1 AND b < 1999.5 GROUP BY a;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query= SELECT a, MAX(b) FROM t1 WHERE a = 1 AND b <= 1999.5 GROUP BY a;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query= SELECT a, MAX(b) FROM t1 WHERE a = 1 AND b between 0 and 1999.5
           GROUP BY a;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query= SELECT a, MIN(b) FROM t1 WHERE a = 1 AND b > 2000.1 GROUP BY a;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query= SELECT a, MIN(b) FROM t1 WHERE a = 1 AND b >= 2000.1 GROUP BY a;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

DROP TABLE t1;

--echo # End of test for Bug#22661012

--echo #
--echo # Bug#24484060 INCORRECT EVALUATION OF MIN/MAX REFERRING
--echo # TO AN OUTER QUERY BLOCK
--echo #

CREATE TABLE t1 (pk INT PRIMARY KEY, nk INT, k INT UNIQUE);
INSERT INTO t1 VALUES (1,1,1),(2,2,2),(4,4,4);
CREATE TABLE t2 (k INT);
INSERT INTO t2 VALUES (1),(2),(4);

ANALYZE TABLE t1, t2;

let query= SELECT t2.k FROM t1 out1, t2 WHERE t2.k = out1.k
AND out1.pk<>3 AND out1.nk = (SELECT MAX(out1.k) FROM t1);

--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;

--echo # Repeat the test with PRIMARY KEY instead of the non-indexed column.
ALTER TABLE t1 DROP COLUMN nk;

let query= SELECT t2.k FROM t1 out1, t2 WHERE t2.k = out1.k
AND out1.pk = (SELECT MAX(out1.k) FROM t1);

--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;

--echo # Inner reference should be optimized.
let query= SELECT t2.k FROM t1 out1, t2 WHERE t2.k = out1.k
AND out1.pk = (SELECT MAX(k) FROM t1);

--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;

let query= SELECT t2.k FROM t1 out1, t2 WHERE t2.k = out1.k
AND out1.pk = (SELECT 2*MIN(k) FROM t1);

--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;

let query= SELECT t2.k FROM t1 out1, t2 WHERE t2.k = out1.k
AND out1.pk = (SELECT SUM(k) - COUNT(k) FROM t1);

--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;

--echo # Combinations of outer and inner references should not be optimized.
let query= SELECT t2.k FROM t1 out1, t2 WHERE t2.k = out1.k
AND out1.pk = (SELECT MIN(out1.k + k) - 1 FROM t1);

--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;

let query= SELECT t2.k FROM t1 out1, t2 WHERE t2.k = out1.k
AND out1.pk = (SELECT out1.k + MIN(k) - 1 FROM t1);

--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;

let query= SELECT t2.k FROM t1 out1, t2 WHERE t2.k = out1.k
AND out1.pk = (SELECT MIN(out1.k) + MIN(k) - 1 FROM t1);

--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;

let query= SELECT t2.k FROM t1 out1, t2 WHERE t2.k = out1.k
AND out1.pk = (SELECT SUM(out1.k) / COUNT(k) FROM t1);

--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;

let query= SELECT t2.k FROM t1 out1, t2 WHERE t2.k = out1.k
AND out1.pk = (SELECT SUM(out1.k) / COUNT(*) FROM t1);

--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;

let query= SELECT t2.k FROM t1 out1, t2 WHERE t2.k = out1.k
AND out1.pk = (SELECT AVG(out1.k) FROM t1);

--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;

DROP TABLE t1, t2;

--echo # End of test for Bug#24484060

--echo #
--echo # Bug#24657798 HANDLER::HA_INDEX_INIT() TRIES TO USE
--echo # AN UNLOCKED CONST TABLE IN OPT_SUM_QUERY()
--echo #

CREATE TABLE t1 (k INT, KEY(k)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (h INT) ENGINE=MyISAM;
INSERT INTO t2 VALUES (3);

let query= SELECT * FROM t1 WHERE (SELECT MIN(t1.k) FROM t2);
--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;
let query= SELECT * FROM t1 WHERE EXISTS (SELECT MIN(t1.k) FROM t1 s1);
--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;
let query= SELECT * FROM t1 WHERE EXISTS (SELECT MIN(t1.k) FROM t2);
--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;

UPDATE t1 SET k=0;

let query= SELECT * FROM t1 WHERE (SELECT MIN(t1.k) FROM t2);
--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;
let query= SELECT * FROM t1 WHERE EXISTS (SELECT MIN(t1.k) FROM t1 s1);
--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;
let query= SELECT * FROM t1 WHERE EXISTS (SELECT MIN(t1.k) FROM t2);
--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;

DROP TABLE t1;

CREATE TABLE t1 (nk INT, k INT, KEY(k)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,1);

let query= SELECT * FROM t1 WHERE nk = (SELECT MIN(t1.k) FROM t1 s1);
--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;
let query= SELECT * FROM t1 WHERE nk = (SELECT MIN(t1.k) FROM t2);
--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;

UPDATE t1 SET nk=0, k=0;

let query= SELECT * FROM t1 WHERE nk = (SELECT MIN(t1.k) FROM t1 s1);
--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;
let query= SELECT * FROM t1 WHERE nk = (SELECT MIN(t1.k) FROM t2);
--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;

DROP TABLE t1;

DROP TABLE t2;

--echo # End of test for Bug#24657798

--echo #
--echo # Bug#24423143 - WRONG RESULTS FOR AGGREGATE QUERY
--echo #

--echo # Test index merge tree scenario
CREATE TABLE a (
  aggr_col int,
  group_by_col int,
  KEY aggr_col_key (aggr_col),
  KEY group_by_col_key (group_by_col, aggr_col)
) ENGINE=InnoDB;

let $DEFAULT_TRACE_MEM_SIZE=1048576; # 1MB
eval set optimizer_trace_max_mem_size=$DEFAULT_TRACE_MEM_SIZE;
set @@session.optimizer_trace='enabled=on';
set end_markers_in_json=on;

INSERT INTO a VALUES (2,3),(5,6),(6,3),(7,NULL),(9,NULL),(10,6);
ANALYZE TABLE a;

SELECT group_by_col, MIN(aggr_col) FROM a
WHERE (group_by_col IN (70, 9)) OR (aggr_col > 2) GROUP BY group_by_col;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT group_by_col, MIN(aggr_col) FROM a
WHERE (group_by_col IN (70 ,9)) OR (aggr_col > 2) GROUP BY group_by_col;

SELECT TRACE RLIKE 'disjuntive_predicate_present' AS OK
                                FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

SELECT group_by_col, MAX(aggr_col) FROM a
WHERE (group_by_col IN (70, 9)) OR (aggr_col < 9) GROUP BY group_by_col;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT group_by_col, MAX(aggr_col) FROM a
WHERE (group_by_col IN (70 , 9)) OR (aggr_col < 9) GROUP BY group_by_col;

SELECT TRACE RLIKE 'disjuntive_predicate_present' AS OK
                                FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

--echo # Test IMPOSSIBLE TREE scenario
ALTER TABLE a DROP KEY aggr_col_key;

SELECT group_by_col, MIN(aggr_col) FROM a
WHERE (group_by_col IN (70 ,9)) OR (aggr_col > 2) GROUP BY group_by_col;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT group_by_col, MIN(aggr_col) FROM a
WHERE (group_by_col IN (70, 9)) OR (aggr_col > 2) GROUP BY group_by_col;

SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' AS OK
                                FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

SELECT group_by_col, MAX(aggr_col) FROM a
WHERE (group_by_col IN (70, 9)) OR (aggr_col < 9) GROUP BY group_by_col;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT group_by_col, MAX(aggr_col) FROM a
WHERE (group_by_col IN (70, 9)) OR (aggr_col < 9) GROUP BY group_by_col;

SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' AS OK
                                FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

--echo # Scenario 3: aggregate field used as equal expression.
SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE (group_by_col IN (3, 9)) OR (aggr_col = 9) GROUP BY group_by_col;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE (group_by_col IN (3, 9)) OR (aggr_col = 9) GROUP BY group_by_col;

SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' AS OK
                                FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

--echo # Scenario 4: non aggregate field used as equal expression.
SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE (group_by_col = 3) OR (aggr_col > 8) GROUP BY group_by_col;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE (group_by_col = 3) OR (aggr_col > 8) GROUP BY group_by_col;

SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' AS OK
                                FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

--echo # Scenario 5: aggregate field used as non-zero expression.
INSERT INTO a VALUES(0, 3);
INSERT INTO a VALUES(0, 9);
INSERT INTO a VALUES(8, 0);
ANALYZE TABLE a;

SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE (group_by_col = 9) OR aggr_col GROUP BY group_by_col;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE group_by_col = 9 OR aggr_col GROUP BY group_by_col;

SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' AS OK
                                FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;


--echo # Scenario 6: non aggregate field used as non-zero expression.
SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE group_by_col OR (aggr_col < 9) GROUP BY group_by_col;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE group_by_col OR (aggr_col < 9) GROUP BY group_by_col;

SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' AS OK
                                FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;


--echo # Scenario 7: aggregate field used in equal exp without a CONST
INSERT INTO a VALUES(1,1),(1,2),(2,1);

SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE aggr_col = group_by_col GROUP BY group_by_col;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE aggr_col = group_by_col GROUP BY group_by_col;

SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' AS OK
                                FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

--echo # Scenario 8: aggregate field used in a non-eq exp without a CONST
SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE aggr_col < group_by_col GROUP BY group_by_col;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE aggr_col < group_by_col GROUP BY group_by_col;

SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' AS OK
                                FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

--echo # Scenario 8
INSERT INTO a VALUES(0,1),(1,0),(0,0);

SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE aggr_col OR group_by_col GROUP BY group_by_col;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE aggr_col OR group_by_col GROUP BY group_by_col;

SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' AS OK
                                FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

--echo # Scenario 9
SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE aggr_col AND group_by_col GROUP BY group_by_col;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE aggr_col AND group_by_col GROUP BY group_by_col;

SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' AS OK
                                FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

--echo # Scenario 10: Added for completion. This fix does not have an impact.
SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE aggr_col<>0 AND group_by_col<>0 GROUP BY group_by_col;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE aggr_col<>0 AND group_by_col<>0 GROUP BY group_by_col;

SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' AS OK
                                FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

--echo # Scenario 11: ITEM_FUNC as an argument of ITEM_FUNC
SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE group_by_col OR (group_by_col < (aggr_col = 1)) GROUP BY group_by_col;

--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT group_by_col, MIN(aggr_col), MAX(aggr_col) FROM a
WHERE group_by_col OR (group_by_col < (aggr_col = 1)) GROUP BY group_by_col;

SELECT TRACE RLIKE 'minmax_keypart_in_disjunctive_query' AS OK
                                FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

SET optimizer_trace_max_mem_size=DEFAULT;
SET optimizer_trace=DEFAULT;
SET end_markers_in_json=DEFAULT;

DROP TABLE a;

--echo # End of test for Bug#24423143

--echo #
--echo # Bug #31475700 REGRESSION: MEMORY LEAK AFTER
--echo #               ITEM_SUM_HYBRID::MIN_MAX_UPDATE_STR_FIELD
--echo #

CREATE TABLE t(a TEXT);
INSERT t VALUES('a'),('a');

SELECT a, MAX(TRIM('1' FROM ~1)) FROM t GROUP BY a;
DROP TABLE t;

--echo #
--echo # Bug#30242457 ASSERTION "!TABLE->CONST_TABLE"
--echo #
CREATE TABLE t2(id1 INT, id2 CHAR (1), id3 INT, value INT,
                KEY (id1,id2,id3)) COLLATE LATIN1_BIN;
CREATE TABLE t1(a INT,b INT, PRIMARY KEY(a)) ENGINE=MEMORY;

let $query = SELECT * FROM t1 WHERE t1.a IN (SELECT MAX(t1.b) FROM t2);

--echo # One row in an in-memory table: row will be read before
--echo # optimizing MAX in subquery. No result set, either when t2 is
--echo # empty or not, since t1.a <> t1.b
INSERT INTO t1 VALUES(1,0);
eval $query;
INSERT INTO t2 VALUES (0, 'a', 3, 4);
eval $query;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
DELETE FROM t1;
DELETE FROM t2;

--echo # Now t1.a = t1.b, still not result if t2 is empty
INSERT INTO t1 VALUES(1,1);
eval $query;

--echo # If t2 has at least one row, we will get a result row
INSERT INTO t2 VALUES (0, 'a', 3, 4);
eval $query;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;

--echo # If we have two rows in t1, t1 will not be read before
--echo # optimization of the subquery, so this didn't use to fail
--echo # before this patch.
INSERT INTO t1 VALUES(0,0);
eval $query;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;

DROP TABLE t1, t2;

--echo # End of test for Bug#30242457

--echo #
--echo # Bug #32769369: SIG11 IN ITEM_FIELD::ITEM_FIELD|SQL/ITEM.CC WITH HYPERGRAPH
--echo #

CREATE TABLE t1 (a INTEGER, KEY (a));
INSERT INTO t1 VALUES (1);
SELECT SQL_BUFFER_RESULT MIN(t1.a) FROM t1, t1 AS t2;
DROP TABLE t1;

--echo # Bug#32266286 : ADDING GROUP BY SLOWS DOWN A QUERY.
--echo #

CREATE TABLE t0 (i0 INTEGER);
INSERT INTO t0 VALUES (1), (2), (3), (4), (5);
INSERT INTO t0 VALUES (6), (7), (8), (9);
CREATE TABLE t1 (f1 INTEGER, f2 INTEGER, KEY (f1,f2));
INSERT INTO t1 SELECT 1, i0 from t0;
INSERT INTO t1 SELECT i0+1, i0*rand()*10 from t0;
INSERT INTO t1 VALUES (1,0);
ANALYZE TABLE t1;

# Should pick loose index scan to do the aggregation. Rows
# looked at should be 1 as the group by attribute is a constant.
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT MIN(f2) FROM t1 WHERE f1=1 GROUP BY f1;
SELECT MIN(f2) FROM t1 WHERE f1=1 GROUP BY f1;
INSERT INTO t1 VALUES (NULL, 1);
INSERT INTO t1 VALUES (NULL, 2);
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT MIN(f2) FROM t1 WHERE f1 IS NULL GROUP BY f1;
SELECT MIN(f2) FROM t1 WHERE f1 IS NULL GROUP BY f1;

DROP TABLE t0,t1;

--echo #
--echo # Bug#33139598: REGRESSION: CRASH IN GET_BEST_GROUP_MIN_MAX
--echo #

CREATE TABLE t1 (f1 ENUM(''), KEY(f1));
INSERT INTO t1 VALUES(''),('');
# Should not crash.
SELECT 1 FROM t1 WHERE f1=UUID() GROUP BY f1 LIMIT 1;
DROP TABLE t1;

--echo #
--echo # Bug#33139598: Assertion `table_independent_conds == nullptr' failed.
--echo #

CREATE TABLE t1 (f1 INTEGER, KEY(f1));
INSERT INTO t1 VALUES (1), (2);

DELIMITER $;
CREATE FUNCTION f()
RETURNS INT DETERMINISTIC
BEGIN
  SET @f:=1;
  RETURN @f;
END$
DELIMITER ;$

# Should not assert
SELECT MAX(f1) FROM t1 WHERE f() = 1;
# Should return NULL
SELECT MAX(f1) FROM t1 WHERE f() = 0;

DROP TABLE t1;
DROP FUNCTION f;

--echo # Bug#34317267: Assertion `!table->file->inited' failed in Mysql 8.0.29.

CREATE TABLE t1 (k INT, KEY(k)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1);

SELECT *
FROM t1 AS ra0 LEFT JOIN t1 AS ra1
     ON ra0.k IN (SELECT MAX(ra0.k) FROM t1);

SELECT *
FROM t1 AS ra0 LEFT JOIN t1 AS ra1
     ON ra0.k IN (SELECT MIN(ra0.k) FROM t1);

SELECT *
FROM t1 AS ra0 LEFT JOIN t1 AS ra1
     ON ra0.k IN (SELECT COUNT(ra0.k) FROM t1);

DROP TABLE t1;

--echo #
--echo # Bug#35842412 WHERE NOT IN with subquery is much slower on 8.1 than 5.7
--echo #
CREATE TABLE t1( b_id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
                 val VARCHAR(100) NOT NULL, val_n VARCHAR(100),
                 KEY ix_val (val, b_id), KEY ix_val_n (val_n, b_id));
CREATE TABLE t2( a_id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
                 val VARCHAR(100) NOT NULL, val_n VARCHAR(100),
                 UNIQUE KEY ix_val (val), UNIQUE KEY ix_val_n (val_n));
INSERT INTO t1(val, val_n)
WITH RECURSIVE DataSource(num, c1, c2) AS (
	SELECT 1, lpad(1,25,'0'), lpad(1,25,'0')
	UNION ALL
	SELECT num + 1, lpad(mod(num+1,30),25,'0'),
      lpad(ELT(num%400 != 0, mod(num+1,30)), 25, '0')
	FROM DataSource
	WHERE num < 1000
)
SELECT c1, c2 from DataSource;
INSERT INTO t2(val, val_n)
WITH RECURSIVE DataSource(num, c1, c2) AS (
	SELECT 1, lpad(1,25,'0'), lpad(1,25,'0')
	UNION ALL
	SELECT num + 1, lpad(num+1,25,'0'), lpad(ELT(num%13 != 0, num+1), 25, '0')
	FROM DataSource
	WHERE num < 30
)
SELECT c1, c2 from DataSource;
ANALYZE TABLE t1, t2;

--echo # Should use group skip scan on t1
let query = SELECT DISTINCT val FROM t1 WHERE EXISTS
    (SELECT * FROM t2 WHERE t2.val = t1.val);
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT val FROM t1 WHERE EXISTS
    (SELECT * FROM t2 WHERE t2.val = t1.val) GROUP BY val;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT DISTINCT val FROM t1 WHERE val IN (SELECT val FROM t2);
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT DISTINCT val FROM t1 WHERE val IN
    (SELECT val FROM t2 WHERE t2.val = t1.val);
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT val FROM t1 WHERE val IN (SELECT val FROM t2 ) GROUP BY val;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT val FROM t1 WHERE val IN
    (SELECT val FROM t2 WHERE t2.val = t1.val ) GROUP BY val;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT DISTINCT val FROM t1 WHERE NOT EXISTS
    (SELECT * FROM t2 WHERE val = 'asd');
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT val FROM t1 WHERE NOT EXISTS
    (SELECT * FROM t2 WHERE val = 'asd' ) GROUP BY val;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

--echo # Should use group skip scan on t1
let query = SELECT DISTINCT val FROM t1 WHERE val NOT IN (SELECT val FROM t2);
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT DISTINCT val FROM t1 WHERE val NOT IN
    (SELECT val FROM t2 WHERE t2.val = t1.val);
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT val FROM t1 WHERE val NOT IN
    (SELECT val FROM t2 ) GROUP BY val;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT val FROM t1 WHERE val NOT IN
    (SELECT val FROM t2 WHERE t2.val = t1.val ) GROUP BY val;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT DISTINCT val FROM t1 WHERE NOT EXISTS
    (SELECT * FROM t2 WHERE t2.val = t1.val);
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT val FROM t1 WHERE NOT EXISTS
    (SELECT * FROM t2 WHERE t2.val = t1.val) GROUP BY val;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

--echo # Should use group skip scan on t1
let query = SELECT DISTINCT val FROM t1 WHERE EXISTS (SELECT * FROM t2);
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT val FROM t1 WHERE EXISTS (SELECT * FROM t2) GROUP BY val;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

--echo # Uses group skip scan with aggregate functions since "t2" is a const
--echo # table. Hypergraph optimizer does not support const table optimization.
--echo # So the plan chosen is different.

let query = SELECT val, MIN(b_id), MAX(b_id) FROM t1 WHERE NOT EXISTS
    (SELECT * FROM t2 WHERE val = 'asd') GROUP BY val;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

--echo # Should not use group skip scan on t1. Group skip scan is not supported
--echo # on multi-table queries with aggregate functions.
let query = SELECT val, MIN(b_id), MAX(b_id) FROM t1 WHERE val
    NOT IN (SELECT val FROM t2) GROUP BY val;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT val, MIN(b_id), MAX(b_id) FROM t1 WHERE EXISTS
    (SELECT * FROM t2) GROUP BY val;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

--echo # Cover nullable column scenarios
--echo # Should use group skip scan on t1
let query = SELECT DISTINCT val_n FROM t1 WHERE EXISTS
    (SELECT * FROM t2 WHERE t2.val_n = t1.val_n);
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT DISTINCT val_n FROM t1 WHERE val_n IN (SELECT val_n FROM t2);
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT DISTINCT val_n FROM t1 WHERE NOT EXISTS
    (SELECT * FROM t2 WHERE val_n = 'asd');
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

--echo # Should use group skip scan on t1
let query = SELECT DISTINCT val_n FROM t1 WHERE val_n NOT IN
    (SELECT val_n FROM t2);
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT DISTINCT val_n FROM t1 WHERE val_n NOT IN
    (SELECT val_n FROM t2 WHERE t2.val_n = t1.val_n);
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT DISTINCT val_n FROM t1 WHERE NOT EXISTS
    (SELECT * FROM t2 WHERE t2.val_n = t1.val_n);
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT val_n FROM t1 WHERE NOT EXISTS
    (SELECT * FROM t2 WHERE t2.val_n = t1.val_n) GROUP BY val_n;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

--echo # Should use group skip scan on t1
let query = SELECT DISTINCT val_n FROM t1 WHERE EXISTS (SELECT * FROM t2);
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

--echo # Uses group skip scan with aggregate functions since "t2" is a const
--echo # table. Hypergraph optimizer does not support const table optimization.
--echo # So the plan chosen is different.
let query = SELECT val_n, MIN(b_id), MAX(b_id) FROM t1 WHERE NOT EXISTS
    (SELECT * FROM t2 WHERE val_n = 'asd') GROUP BY val_n;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

--echo # Should not use group skip scan on t1. Group skip scan is not supported
--echo # on multi-table queries with aggregate functions.
let query = SELECT val_n, MIN(b_id), MAX(b_id) FROM t1 WHERE val_n
    NOT IN (SELECT val_n FROM t2) GROUP BY val_n;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

let query = SELECT val_n, MIN(b_id), MAX(b_id) FROM t1 WHERE EXISTS
    (SELECT * FROM t2) GROUP BY val_n;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=TREE $query;
eval $query;

DROP TABLE t1, t2;
